package com.epoint.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.druid.util.StringUtils;
import com.epoint.entity.BookInfo;
import com.epoint.utils.JDBCUtils;

/**
 * @Description 图书信息列表
 * @author ylc
 */
public class BookInfoDao {

	/**
	 * 获取图书信息列表
	 * 
	 * @param bookName
	 * @param bookType
	 * @param pageIndex
	 * @param pageSize
	 * @param sortField
	 * @param sortOrder
	 * @return
	 */
	public List<BookInfo> getBookList(String bookName, String bookType, int pageIndex, int pageSize, String sortField,
			String sortOrder) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		List<BookInfo> bookList = new ArrayList<BookInfo>();
		List<Object> params = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("select * from bookinfo where 1=1");

		// 查询条件
		if (!StringUtils.isEmpty(bookName)) {
			sb.append(" and bookname like '%" + bookName + "%'");
		}
		if (!StringUtils.isEmpty(bookType)) {
			sb.append(" and bookType = ?");
			params.add(bookType);
		}
		// 排序条件
		if (!StringUtils.isEmpty(sortOrder) && !StringUtils.isEmpty(sortField)) {
			sb.append(" order by " + sortField + " " + sortOrder);
		}
		// 分页条件
		if (-1 != pageIndex && -1 != pageSize) {
			sb.append(" limit ?,?");
			params.add(pageIndex * pageSize);
			params.add(pageSize);
		}
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = sb.toString();
			pstmt = conn.prepareStatement(sql);

			// 给?赋值
			int sizeCount = params.size();
			for (int i = 0; i < sizeCount; i++) {
				if ((sizeCount - i) <= 2) {
					pstmt.setInt(i + 1, Integer.parseInt(params.get(i).toString()));
				} else {
					pstmt.setString(i + 1, params.get(i).toString());
				}
			}
			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				BookInfo bookInfo = new BookInfo();
				bookInfo.setBookId(rs.getString("bookid"));
				bookInfo.setBookName(rs.getString("bookname"));
				bookInfo.setPublisher(rs.getString("publisher"));
				bookInfo.setAuthor(rs.getString("author"));
				bookInfo.setBookType(rs.getInt("booktype"));
				bookInfo.setRemain(rs.getInt("remain"));

				bookList.add(bookInfo);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}

		return bookList;
	}

	/**
	 * 获取图书列表总数
	 * 
	 * @param bookName
	 * @param bookType
	 * @return
	 */
	public int getBookListCount(String bookName, String bookType) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count = 0;

		List<Object> params = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("select count(1) from bookinfo where 1=1");

		// 查询条件
		if (!StringUtils.isEmpty(bookName)) {
			sb.append(" and bookname like '%" + bookName + "%'");
		}
		if (!StringUtils.isEmpty(bookType)) {
			sb.append(" and bookType = ?");
			params.add(bookType);
		}

		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = sb.toString();
			pstmt = conn.prepareStatement(sql);

			// 给?赋值
			int sizeCount = params.size();
			for (int i = 0; i < sizeCount; i++) {
				if ((sizeCount - i) <= 2) {
					pstmt.setInt(i + 1, Integer.parseInt(params.get(i).toString()));
				} else {
					pstmt.setString(i + 1, params.get(i).toString());
				}
			}
			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return count;
	}

	/**
	 * 查询所有图书信息
	 * 
	 * @return
	 */
	public List<BookInfo> getAll() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		List<BookInfo> bookList = new ArrayList<BookInfo>();
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select * from bookinfo where remain > 0";
			pstmt = conn.prepareStatement(sql);

			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				BookInfo bookInfo = new BookInfo();
				bookInfo.setBookId(rs.getString("bookid"));
				bookInfo.setBookName(rs.getString("bookname"));

				bookList.add(bookInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return bookList;
	}

	/**
	 * 新增图书信息
	 * 
	 * @param bookInfo
	 * @return
	 */
	public int addBookInfo(BookInfo bookInfo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count = 0;

		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "INSERT INTO bookmanager.bookinfo(bookid, bookname, publisher, author, booktype, remain) VALUES(?, ?, ?, ?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			// 给?赋值
			pstmt.setString(1, bookInfo.getBookId());
			pstmt.setString(2, bookInfo.getBookName());
			pstmt.setString(3, bookInfo.getPublisher());
			pstmt.setString(4, bookInfo.getAuthor());
			pstmt.setInt(5, bookInfo.getBookType());
			pstmt.setInt(6, bookInfo.getRemain());

			// 执行查询，获取结果集
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return count;
	}

	/**
	 * 根据作者和图书名称查询图书编号
	 * 
	 * @param author
	 * @param bookName
	 * @return
	 */
	public String getBookIdByAuthorAndBookName(String author, String bookName) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String res = "";

		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select bookid from bookinfo where author = ? and bookname = ?";
			pstmt = conn.prepareStatement(sql);
			// 给?赋值
			pstmt.setString(1, author);
			pstmt.setString(2, bookName);

			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				res = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return res;
	}

	/**
	 * 根据bookId查询bookInfo对象
	 * 
	 * @param bookId
	 * @return
	 */
	public BookInfo findById(String bookId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BookInfo bookInfo = new BookInfo();

		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select * from bookinfo where bookid = ?";
			pstmt = conn.prepareStatement(sql);
			// 给?赋值
			pstmt.setString(1, bookId);

			// 执行查询，获取结果集
			rs = pstmt.executeQuery();

			while (rs.next()) {
				bookInfo.setBookId(rs.getString("bookid"));
				bookInfo.setBookName(rs.getString("bookname"));
				bookInfo.setPublisher(rs.getString("publisher"));
				bookInfo.setAuthor(rs.getString("author"));
				bookInfo.setBookType(rs.getInt("booktype"));
				bookInfo.setRemain(rs.getInt("remain"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return bookInfo;
	}

	/**
	 * 更新BookInfo对象
	 * 
	 * @param bookInfo
	 * @return
	 */
	public int updateBookInfo(BookInfo bookInfo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int res = 0;

		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "UPDATE bookinfo SET bookname=?, publisher=?, author=?, booktype=?, remain=? WHERE bookid=?";
			pstmt = conn.prepareStatement(sql);
			// 给?赋值
			pstmt.setString(1, bookInfo.getBookName());
			pstmt.setString(2, bookInfo.getPublisher());
			pstmt.setString(3, bookInfo.getAuthor());
			pstmt.setInt(4, bookInfo.getBookType());
			pstmt.setInt(5, bookInfo.getRemain());
			pstmt.setString(6, bookInfo.getBookId());

			// 执行查询，获取结果
			res = pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(pstmt, conn);
		}
		return res;
	}

	/**
	 * 根据ID删除图书信息,级联删除借阅表中数据
	 * 
	 * @param bookId
	 * @return
	 */
	public int deleteBookInfoByBookId(String bookId) {
		Connection conn = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		int res = 0;

		try {
			conn = JDBCUtils.getConnection();
			conn.setAutoCommit(false);
			
			// 删除借阅表数据
			String delBorrowInfoSql = "DELETE FROM bookmanager.borrowinfo WHERE bookid=?";
			pstmt1 = conn.prepareStatement(delBorrowInfoSql);
			pstmt1.setString(1, bookId);
			res = pstmt1.executeUpdate();
			
			// 删除图书数据
			String delBookInfoSql = "DELETE FROM bookmanager.bookinfo WHERE bookid=?";
			pstmt2 = conn.prepareStatement(delBookInfoSql);
			pstmt2.setString(1, bookId);
			res = pstmt2.executeUpdate();

			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(pstmt1, conn);
			JDBCUtils.close(pstmt2);
		}
		return res;
	}
}
